package LandR.personal.personalHelp.function_jsp;


import LandR.personal.personalHelp.Users.User;
import LandR.personal.personalHelp.function_mysql.ConnectionMethod;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * 这个类包含一些操作数据库的代码
 * 1）void insertUserData(User user) 添加新用户信息
 * 2）void updateUserData(User user) 更新用户信息
 * 3）User selectUserData(User user) 通过id查询用户信息
 */
public class JspPageUseMethod {

    /**
     * inserUserData()
     * 用于向数据库中添加用户信息
     * 参数是一个user对象
     *
     * @param user
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void insertUserData(User user) throws SQLException, ClassNotFoundException {

        Connection conn = ConnectionMethod.getConnection();
        String sql = "insert into usersdata (username,gender,age,specificAddress,email,phonenumber,introduce,imageaddress,specificage)values(?,?,?,?,?,?,?,?,?)";
        PreparedStatement ppst = ConnectionMethod.getPrepareStatement(sql);
        try {
            String extra = null;
            ppst.setString(1, user.getUsername());
            System.out.println("username上传成功");
            ppst.setString(2, user.getGender());
            System.out.println("gender上传成功");
            ppst.setInt(3, user.getAge());
            System.out.println("age上传成功");
            ppst.setString(4, user.getSpecificAddress());
            System.out.println("specificAddress上传成功");
            ppst.setString(5, user.getEmail());
            System.out.println("email上传成功");
            ppst.setString(6, user.getPhoneNumber());
            System.out.println("phoneNumber上传成功");
            ppst.setString(7, user.getIntroduce());
            System.out.println("introduce上传成功");
            ppst.setString(8, user.getImageAddress());
            System.out.println("imageAddress上传成功");
            ppst.setString(9, user.getSpecificAge());
            System.out.println("SpecificAge上传成功");
            ppst.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            ConnectionMethod connMethod = new ConnectionMethod();
            ConnectionMethod.close(ppst);
            System.out.println("preparestatement接口关闭成功");
            ConnectionMethod.close(conn);
            System.out.println("connection接口关闭成功");

            System.out.println("用户信息上传成功");

        }
    }

    /**
     * updateUserData()
     * 用于更新数据库的元素
     * 参数需要从数据库调出的元素和对应的id
     * 传入时需要初始的id和更改后的信息，和没有更改的信息
     *
     * @param user
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public void updateUserData(User user) throws SQLException, ClassNotFoundException {
        Connection conn = ConnectionMethod.getConnection();
        String sql = "update usersdata set username=?,gender=?,age=?,specificAddress=?,email=?,phonenumber=?,introduce=?,imageaddress=?,specificage=? where id=?";
        PreparedStatement ppst = ConnectionMethod.getPrepareStatement(sql);
        ppst.setString(1, user.getUsername());
        System.out.println("username更新成功");
        ppst.setString(2, user.getGender());
        System.out.println("gender更新成功");
        ppst.setInt(3, user.getAge());
        System.out.println("age更新成功");
        ppst.setString(4, user.getSpecificAddress());
        System.out.println("specifiaddress更新成功");
        ppst.setString(5, user.getEmail());
        System.out.println("email更新成功");
        ppst.setString(6, user.getPhoneNumber());
        System.out.println("phoneNumber更新成功");
        ppst.setString(7, user.getIntroduce());
        System.out.println("introduce更新成功");
        ppst.setString(8, user.getImageAddress());
        System.out.println("imageAddress更新成功");
        ppst.setString(9, user.getSpecificAge());
        System.out.println("sepecificAge更新成功");
        ppst.setInt(10, user.getId());
        System.out.println("id定位成功");
        ppst.executeUpdate();
        System.out.println("更新信息上传成功");
        ConnectionMethod connMethod = new ConnectionMethod();
        ConnectionMethod.close(ppst);
        System.out.println("preparestatement接口关闭成功");
        System.out.println("用户信息更新成功");
        ConnectionMethod.close(conn);
        System.out.println("connection接口关闭成功");


    }

    /**
     * selectUserData(User user)
     * 唯一调用的方式就是给对象user赋值一个参数id
     * 通过id 查询查询到唯一用户
     * 用户的id是唯一确定的
     * 返回一个user对象，被赋值的user对象
     *
     * @param user
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public User selectUserData(User user) throws SQLException, ClassNotFoundException {
        Integer id = user.getId();
        if (id != null) {
            Connection conn = ConnectionMethod.getConnection();
            System.out.println("数据库连接创建成功");
            String sql = "select * from usersdata where id = ?;";
            PreparedStatement ppst = ConnectionMethod.getPrepareStatement(sql);
            System.out.println("PrepareSatement接口创建成功");
            ppst.setInt(1, id);
            ResultSet rs = (ResultSet) ppst.executeQuery();
            while (rs.next()) {
                user.setUsername(rs.getString("username"));
                user.setAge(rs.getInt("age"));
                user.setGender(rs.getString("gender"));
                user.setSpecificAddress(rs.getString("specificAddress"));
                user.setEmail(rs.getString("email"));
                user.setPhoneNumber(rs.getString("phonenumber"));
                user.setIntroduce(rs.getString("introduce"));
                user.setImageAddress(rs.getString("imageaddress"));
                user.setSpecificAge(rs.getString("specificage"));
            }
            System.out.println("信息获取成功");
            ConnectionMethod connMethod = new ConnectionMethod();

            System.out.println("用户信息获取成功");

            ConnectionMethod.close(rs);
            System.out.println("resultSet接口关闭成功");
            ConnectionMethod.close(ppst);
            System.out.println("preparestatement接口关闭成功");
            ConnectionMethod.close(conn);
            System.out.println("connection接口关闭成功");

            return user;
        } else {
            return null;
        }
    }

    public User distDate(User user) {
        String specificAge = user.getSpecificAge();
        int index = specificAge.indexOf("/");
        int indexL = specificAge.lastIndexOf("/");
        user.setYear(specificAge.substring(0, index));
        user.setMonth(specificAge.substring(index + 1, indexL));
        user.setDay(specificAge.substring(indexL + 1, specificAge.length()));
        return user;
    }

    public User distArea(User user) {
        String address = user.getSpecificAddress();
        int index = address.indexOf("/");
        int indexL = address.lastIndexOf("/");
        user.setProvince(address.substring(0, index));
        user.setCity(address.substring(index + 1, indexL));
        user.setCountly(address.substring(indexL + 1, address.length()));
        return user;
    }
}
